﻿/*
DELETE FROM [Production].[ProductCategory];
DELETE FROM [Production].[ProductCategory_Shadow];
*/

DECLARE @ProductCategoryID1 INT;
DECLARE @ProductCategoryID2 INT;
DECLARE @ProductCategoryID3 INT;

-- Add three categories
EXEC @ProductCategoryID1 = [Production].[aspProductCategory_Insert]
	@Name = 'Test Category 1',
	@ExternalID = 'Yo yo this is a demo. Fool';

EXEC @ProductCategoryID2 = [Production].[aspProductCategory_Insert]
	@Name = 'Test Category 2',
	@ExternalID = 'A very long type of external reference indeed';

EXEC @ProductCategoryID3 = [Production].[aspProductCategory_Insert]
	@Name = 'Test Category 3',
	@ExternalID = 'Row, row, row your boat. Fast. Faster!';

-- change the name of category 2 not via the stored procedure
UPDATE [Production].[ProductCategory] SET [Name] = 'Test Category 2 (Amended)'
WHERE ProductCategoryID = @ProductCategoryID2;

-- delete category 3
EXEC [Production].[aspProductCategory_Delete]
	@ProductCategoryID = @ProductCategoryID3;

SELECT * FROM [Production].[ProductCategory];
SELECT * FROM [Production].[ProductCategory_Shadow];

-- FINALLY TRY REDUCING THE SIZE OF THE EXTERNALID COLUMN NOW IT HAS DATA WAS  NVARCHAR (50)
-- FAILS. SWEET.

--EXECUTE AS USER = 'todocuser';

--ALTER TABLE [Production].[ProductCategory] ALTER COLUMN [ExternalID] NVARCHAR (5);

--REVERT;